/*
 Navicat Premium Data Transfer

 Source Server         : mypetstore
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : localhost:3306
 Source Schema         : hotel_klt

 Target Server Type    : MySQL
 Target Server Version : 80020
 File Encoding         : 65001

 Date: 15/07/2020 19:34:20
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ADMIN
-- ----------------------------
DROP TABLE IF EXISTS `ADMIN`;
CREATE TABLE `ADMIN`
(
    `adminname` varchar(255) DEFAULT NULL,
    `password`  varchar(255) DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of ADMIN
-- ----------------------------
BEGIN;
INSERT INTO `ADMIN`
VALUES ('admin', 'admin');
COMMIT;

-- ----------------------------
-- Table structure for admin_info
-- ----------------------------
DROP TABLE IF EXISTS `admin_info`;
CREATE TABLE `admin_info`
(
    `adminname` varchar(25) NOT NULL,
    `password`  varchar(25) DEFAULT NULL,
    PRIMARY KEY (`adminname`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of admin_info
-- ----------------------------
BEGIN;
INSERT INTO `admin_info`
VALUES ('admin', 'admin');
INSERT INTO `admin_info`
VALUES ('qwe', 'qwe');
COMMIT;

-- ----------------------------
-- Table structure for app_user
-- ----------------------------
DROP TABLE IF EXISTS `app_user`;
CREATE TABLE `app_user`
(
    `username`   varchar(10) DEFAULT NULL,
    `password`   varchar(50) DEFAULT NULL,
    `first_name` varchar(10) DEFAULT NULL,
    `last_name`  varchar(10) DEFAULT NULL,
    `email`      varchar(20) DEFAULT NULL,
    `token`      varchar(20) DEFAULT NULL,
    `id`         int         DEFAULT NULL,
    UNIQUE KEY `UK_1j9d9a06i600gd43uu3km82jw` (`email`),
    UNIQUE KEY `UK_3k4cplvh82srueuttfkwnylq0` (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of app_user
-- ----------------------------
BEGIN;
INSERT INTO `app_user`
VALUES ('ACID1', '74cf3f0ef3065fa151544771eb783c46', 'qwr', 'wer', 'acid@163.com', NULL, 1);
INSERT INTO `app_user`
VALUES ('manager', '1d0258c2440a8d19e716292b231e3190', 'test', 'Manager', 'manager@HHH.com', NULL, 2);
INSERT INTO `app_user`
VALUES ('ACID', '83217e6c3d44a053bd4438eb86eefe28', 'test', 'manager', 'yueyunfei@111.com', NULL, 3);
INSERT INTO `app_user`
VALUES ('asdf', '912ec803b2ce49e4a541068d495ab570', 'yunfei', 'yuuuuu', 'asdf@163.com', NULL, NULL);
INSERT INTO `app_user`
VALUES ('admin', '21232f297a57a5a743894a0e4a801fc3', 'test', 'admin', 'admin@163.com', NULL, 4);
COMMIT;

-- ----------------------------
-- Table structure for APP_USER_USER_PROFILE
-- ----------------------------
DROP TABLE IF EXISTS `APP_USER_USER_PROFILE`;
CREATE TABLE `APP_USER_USER_PROFILE`
(
    `user_id`         int DEFAULT NULL,
    `user_profile_id` int DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of APP_USER_USER_PROFILE
-- ----------------------------
BEGIN;
INSERT INTO `APP_USER_USER_PROFILE`
VALUES (2, 102);
INSERT INTO `APP_USER_USER_PROFILE`
VALUES (3, 102);
INSERT INTO `APP_USER_USER_PROFILE`
VALUES (0, 102);
INSERT INTO `APP_USER_USER_PROFILE`
VALUES (1, 103);
INSERT INTO `APP_USER_USER_PROFILE`
VALUES (4, 101);
COMMIT;

-- ----------------------------
-- Table structure for booking
-- ----------------------------
DROP TABLE IF EXISTS `booking`;
CREATE TABLE `booking`
(
    `room_id`        int                                                    DEFAULT NULL,
    `id`             int                                                    DEFAULT NULL,
    `arrival_time`   datetime                                               DEFAULT NULL,
    `comment`        varchar(255)                                           DEFAULT NULL,
    `departure_time` datetime                                               DEFAULT NULL,
    `people`         int                                                    DEFAULT NULL,
    `room_booked`    varchar(2)                                             DEFAULT NULL,
    `status`         varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
    `user_id`        int                                                    DEFAULT NULL,
    `room_type_id`   int                                                    DEFAULT NULL,
    KEY `FKq83pan5xy2a6rn0qsl9bckqai` (`room_id`),
    CONSTRAINT `FKq83pan5xy2a6rn0qsl9bckqai` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of booking
-- ----------------------------
BEGIN;
INSERT INTO `booking`
VALUES (444, 444, '2020-07-16 00:00:00', '12332', '2020-07-18 00:00:00', 6, '1', 'CONFIRMED', 4, 2);
COMMIT;

-- ----------------------------
-- Table structure for check_in_info
-- ----------------------------
DROP TABLE IF EXISTS `check_in_info`;
CREATE TABLE `check_in_info`
(
    `check_in_id`      varchar(20) NOT NULL,
    `check_in_time`    varchar(40)  DEFAULT NULL,
    `check_in_name`    varchar(20)  DEFAULT NULL,
    `credentials_type` varchar(20)  DEFAULT NULL,
    `credentials_num`  varchar(20)  DEFAULT NULL,
    `continue_bool`    varchar(255) DEFAULT NULL,
    `housing_days`     varchar(5)   DEFAULT NULL,
    `CHECK_OUT_TIME`   datetime     DEFAULT NULL,
    PRIMARY KEY (`check_in_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of check_in_info
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for consumption_list_info
-- ----------------------------
DROP TABLE IF EXISTS `consumption_list_info`;
CREATE TABLE `consumption_list_info`
(
    `list_id`     varchar(20) NOT NULL,
    `goods_name`  varchar(5)   DEFAULT NULL,
    `goods_num`   int          DEFAULT NULL,
    `goods_price` double       DEFAULT NULL,
    `use_time`    varchar(40)  DEFAULT NULL,
    `check_in_id` varchar(20)  DEFAULT NULL,
    `total`       varchar(255) DEFAULT NULL,
    PRIMARY KEY (`list_id`),
    KEY `check_in_id` (`check_in_id`),
    CONSTRAINT `check_in_id` FOREIGN KEY (`check_in_id`) REFERENCES `check_in_info` (`check_in_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of consumption_list_info
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for customer_info
-- ----------------------------
DROP TABLE IF EXISTS `customer_info`;
CREATE TABLE `customer_info`
(
    `check_in_name`     varchar(20) NOT NULL,
    `credentials_type`  varchar(20) NOT NULL,
    `credentials_num`   varchar(20) NOT NULL,
    `vip_type`          varchar(255) DEFAULT NULL,
    `last_checkin_time` varchar(40)  DEFAULT NULL,
    PRIMARY KEY (`check_in_name`, `credentials_type`, `credentials_num`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of customer_info
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for FEEDBACK
-- ----------------------------
DROP TABLE IF EXISTS `FEEDBACK`;
CREATE TABLE `FEEDBACK`
(
    `ID`      int          DEFAULT NULL,
    `NAME`    varchar(255) DEFAULT NULL,
    `EMAIL`   varchar(255) DEFAULT NULL,
    `COMMENT` varchar(255) DEFAULT NULL,
    `RATING`  int          DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of FEEDBACK
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for room
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room`
(
    `id`          int NOT NULL,
    `status`      varchar(255)   DEFAULT NULL,
    `type`        varchar(10)    DEFAULT NULL,
    `name`        varchar(10)    DEFAULT NULL,
    `price`       decimal(10, 2) DEFAULT NULL,
    `CAPACITY`    int            DEFAULT NULL,
    `DESCRIPTION` varchar(255)   DEFAULT NULL,
    `BATH`        tinyint(1)     DEFAULT NULL,
    `BED`         int            DEFAULT NULL,
    `INTERNET`    tinyint(1)     DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `UK_4l8mm4fqoos6fcbx76rvqxer` (`name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of room
-- ----------------------------
BEGIN;
INSERT INTO `room`
VALUES (412, 'UNVERIFIED', '1', 'Room 412', 1099.00, 2, '&#23485;&#25950;&#26126;&#20142;', 1, 2, 1);
INSERT INTO `room`
VALUES (423, 'VERIFIED', '2', 'Room 423', 2480.00, 3, 'sea ', 1, 3, 1);
INSERT INTO `room`
VALUES (444, 'VERIFIED', '3', 'Room 444', 8888.00, 4, NULL, 1, 3, 1);
COMMIT;

-- ----------------------------
-- Table structure for ROOM_IMAGE
-- ----------------------------
DROP TABLE IF EXISTS `ROOM_IMAGE`;
CREATE TABLE `ROOM_IMAGE`
(
    `room` int          DEFAULT NULL,
    `ID`   int          DEFAULT NULL,
    `NAME` varchar(255) DEFAULT NULL,
    `URL`  varchar(255) DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of ROOM_IMAGE
-- ----------------------------
BEGIN;
INSERT INTO `ROOM_IMAGE`
VALUES (0, NULL, 'Room423_0', 'Room423_0.png');
COMMIT;

-- ----------------------------
-- Table structure for room_info
-- ----------------------------
DROP TABLE IF EXISTS `room_info`;
CREATE TABLE `room_info`
(
    `room_id`     varchar(20) NOT NULL,
    `room_name`   varchar(50)  DEFAULT NULL,
    `room_type`   varchar(20)  DEFAULT NULL,
    `floor`       varchar(3)   DEFAULT NULL,
    `bed_num`     int          DEFAULT NULL,
    `room_status` varchar(50)  DEFAULT NULL,
    `price`       int          DEFAULT NULL,
    `deposit`     int          DEFAULT NULL,
    `remarks`     varchar(200) DEFAULT NULL,
    `creat_time`  varchar(40)  DEFAULT NULL,
    PRIMARY KEY (`room_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of room_info
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for room_reserve_info
-- ----------------------------
DROP TABLE IF EXISTS `room_reserve_info`;
CREATE TABLE `room_reserve_info`
(
    `reserver_id`      varchar(20) NOT NULL,
    `customer_name`    varchar(50)  DEFAULT NULL,
    `customer_tel`     varchar(11)  DEFAULT NULL,
    `credentials_type` varchar(20)  DEFAULT NULL,
    `reserve_room_id`  varchar(20)  DEFAULT NULL,
    `reserve_time`     varchar(40)  DEFAULT NULL,
    `keep_time`        varchar(40)  DEFAULT NULL,
    `vip_id`           varchar(20)  DEFAULT NULL,
    `cancle_bool`      varchar(255) DEFAULT NULL,
    `cancle_time`      varchar(40)  DEFAULT NULL,
    `cancle_reason`    varchar(50)  DEFAULT NULL,
    `check_in_bool`    varchar(255) DEFAULT NULL,
    `check_in_time`    varchar(40)  DEFAULT NULL,
    `reserve_deposit`  int          DEFAULT NULL,
    PRIMARY KEY (`reserver_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of room_reserve_info
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for room_type
-- ----------------------------
DROP TABLE IF EXISTS `room_type`;
CREATE TABLE `room_type`
(
    `ID`         int            DEFAULT NULL,
    `BASE_PRICE` decimal(10, 2) DEFAULT NULL,
    `TYPE`       varchar(255)   DEFAULT NULL,
    UNIQUE KEY `UK_l6r0uf0572i4swkemkwrx9bgn` (`TYPE`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of room_type
-- ----------------------------
BEGIN;
INSERT INTO `room_type`
VALUES (1, 1500.00, 'FAMILY');
INSERT INTO `room_type`
VALUES (2, 2000.00, 'DELUXE');
INSERT INTO `room_type`
VALUES (3, 2500.00, 'EXECUTIVE');
COMMIT;

-- ----------------------------
-- Table structure for user_profile
-- ----------------------------
DROP TABLE IF EXISTS `user_profile`;
CREATE TABLE `user_profile`
(
    `ID`   int          DEFAULT NULL,
    `TYPE` varchar(255) DEFAULT NULL,
    UNIQUE KEY `UK_k6d5iiad3vb5isxj1munty17o` (`TYPE`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- ----------------------------
-- Records of user_profile
-- ----------------------------
BEGIN;
INSERT INTO `user_profile`
VALUES (101, 'ADMIN');
INSERT INTO `user_profile`
VALUES (102, 'MANAGER');
INSERT INTO `user_profile`
VALUES (103, 'USER');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
